/**
 * 基础模型类
 * Base Model Class
 */

const { query, transaction } = require('../connection');
const logger = require('../../utils/logger');

class BaseModel {
  constructor(tableName) {
    this.tableName = tableName;
    this.primaryKey = 'id';
  }

  /**
   * 查找单条记录
   */
  async findById(id, fields = '*') {
    try {
      const sql = `SELECT ${fields} FROM ${this.tableName} WHERE ${this.primaryKey} = ? LIMIT 1`;
      const { rows } = await query(sql, [id]);
      return rows[0] || null;
    } catch (error) {
      logger.error(`查找记录失败 / Failed to find record by id: ${id}`, {
        table: this.tableName,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 根据条件查找单条记录
   */
  async findOne(conditions = {}, fields = '*') {
    try {
      const { whereClause, values } = this.buildWhereClause(conditions);
      const sql = `SELECT ${fields} FROM ${this.tableName} ${whereClause} LIMIT 1`;
      const { rows } = await query(sql, values);
      return rows[0] || null;
    } catch (error) {
      logger.error('查找单条记录失败 / Failed to find one record', {
        table: this.tableName,
        conditions,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 根据条件查找多条记录
   */
  async findMany(conditions = {}, options = {}) {
    try {
      const {
        fields = '*',
        orderBy = `${this.primaryKey} DESC`,
        limit = null,
        offset = 0
      } = options;

      const { whereClause, values } = this.buildWhereClause(conditions);
      
      let sql = `SELECT ${fields} FROM ${this.tableName} ${whereClause}`;
      
      if (orderBy) {
        sql += ` ORDER BY ${orderBy}`;
      }
      
      if (limit) {
        sql += ` LIMIT ${limit}`;
        if (offset > 0) {
          sql += ` OFFSET ${offset}`;
        }
      }

      const { rows } = await query(sql, values);
      return rows;
    } catch (error) {
      logger.error('查找多条记录失败 / Failed to find many records', {
        table: this.tableName,
        conditions,
        options,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 分页查询
   */
  async paginate(conditions = {}, options = {}) {
    try {
      const {
        page = 1,
        pageSize = 20,
        fields = '*',
        orderBy = `${this.primaryKey} DESC`
      } = options;

      const offset = (page - 1) * pageSize;

      // 获取总数
      const { whereClause, values } = this.buildWhereClause(conditions);
      const countSql = `SELECT COUNT(*) as total FROM ${this.tableName} ${whereClause}`;
      const { rows: countRows } = await query(countSql, values);
      const total = countRows[0].total;

      // 获取数据
      const data = await this.findMany(conditions, {
        fields,
        orderBy,
        limit: pageSize,
        offset
      });

      return {
        data,
        pagination: {
          page,
          pageSize,
          total,
          totalPages: Math.ceil(total / pageSize),
          hasNext: page * pageSize < total,
          hasPrev: page > 1
        }
      };
    } catch (error) {
      logger.error('分页查询失败 / Failed to paginate records', {
        table: this.tableName,
        conditions,
        options,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 创建记录
   */
  async create(data) {
    try {
      const fields = Object.keys(data);
      const values = Object.values(data);
      const placeholders = fields.map(() => '?').join(', ');
      
      const sql = `INSERT INTO ${this.tableName} (${fields.join(', ')}) VALUES (${placeholders})`;
      const { rows } = await query(sql, values);
      
      const insertId = rows.insertId;
      return await this.findById(insertId);
    } catch (error) {
      logger.error('创建记录失败 / Failed to create record', {
        table: this.tableName,
        data,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 批量创建记录
   */
  async createMany(dataArray) {
    if (!Array.isArray(dataArray) || dataArray.length === 0) {
      return [];
    }

    try {
      return await transaction(async (connection) => {
        const results = [];
        
        for (const data of dataArray) {
          const fields = Object.keys(data);
          const values = Object.values(data);
          const placeholders = fields.map(() => '?').join(', ');
          
          const sql = `INSERT INTO ${this.tableName} (${fields.join(', ')}) VALUES (${placeholders})`;
          const [result] = await connection.execute(sql, values);
          
          const insertId = result.insertId;
          const [newRecord] = await connection.execute(
            `SELECT * FROM ${this.tableName} WHERE ${this.primaryKey} = ?`,
            [insertId]
          );
          
          results.push(newRecord[0]);
        }
        
        return results;
      });
    } catch (error) {
      logger.error('批量创建记录失败 / Failed to create many records', {
        table: this.tableName,
        count: dataArray.length,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 更新记录
   */
  async update(id, data) {
    try {
      const fields = Object.keys(data);
      const values = Object.values(data);
      const setClause = fields.map(field => `${field} = ?`).join(', ');
      
      const sql = `UPDATE ${this.tableName} SET ${setClause} WHERE ${this.primaryKey} = ?`;
      await query(sql, [...values, id]);
      
      return await this.findById(id);
    } catch (error) {
      logger.error(`更新记录失败 / Failed to update record: ${id}`, {
        table: this.tableName,
        data,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 根据条件更新记录
   */
  async updateMany(conditions, data) {
    try {
      const fields = Object.keys(data);
      const values = Object.values(data);
      const setClause = fields.map(field => `${field} = ?`).join(', ');
      
      const { whereClause, values: whereValues } = this.buildWhereClause(conditions);
      
      const sql = `UPDATE ${this.tableName} SET ${setClause} ${whereClause}`;
      const { rows } = await query(sql, [...values, ...whereValues]);
      
      return rows.affectedRows;
    } catch (error) {
      logger.error('批量更新记录失败 / Failed to update many records', {
        table: this.tableName,
        conditions,
        data,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 删除记录
   */
  async delete(id) {
    try {
      const sql = `DELETE FROM ${this.tableName} WHERE ${this.primaryKey} = ?`;
      const { rows } = await query(sql, [id]);
      return rows.affectedRows > 0;
    } catch (error) {
      logger.error(`删除记录失败 / Failed to delete record: ${id}`, {
        table: this.tableName,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 根据条件删除记录
   */
  async deleteMany(conditions) {
    try {
      const { whereClause, values } = this.buildWhereClause(conditions);
      const sql = `DELETE FROM ${this.tableName} ${whereClause}`;
      const { rows } = await query(sql, values);
      return rows.affectedRows;
    } catch (error) {
      logger.error('批量删除记录失败 / Failed to delete many records', {
        table: this.tableName,
        conditions,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 软删除记录
   */
  async softDelete(id) {
    try {
      const data = {
        is_deleted: 1,
        deleted_at: new Date()
      };
      return await this.update(id, data);
    } catch (error) {
      logger.error(`软删除记录失败 / Failed to soft delete record: ${id}`, {
        table: this.tableName,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 统计记录数量
   */
  async count(conditions = {}) {
    try {
      const { whereClause, values } = this.buildWhereClause(conditions);
      const sql = `SELECT COUNT(*) as count FROM ${this.tableName} ${whereClause}`;
      const { rows } = await query(sql, values);
      return rows[0].count;
    } catch (error) {
      logger.error('统计记录失败 / Failed to count records', {
        table: this.tableName,
        conditions,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 检查记录是否存在
   */
  async exists(conditions) {
    try {
      const count = await this.count(conditions);
      return count > 0;
    } catch (error) {
      logger.error('检查记录存在性失败 / Failed to check record existence', {
        table: this.tableName,
        conditions,
        error: error.message
      });
      throw error;
    }
  }

  /**
   * 构建WHERE子句
   */
  buildWhereClause(conditions) {
    const keys = Object.keys(conditions);
    
    if (keys.length === 0) {
      return { whereClause: '', values: [] };
    }

    const clauses = [];
    const values = [];

    keys.forEach(key => {
      const value = conditions[key];
      
      if (value === null) {
        clauses.push(`${key} IS NULL`);
      } else if (Array.isArray(value)) {
        const placeholders = value.map(() => '?').join(', ');
        clauses.push(`${key} IN (${placeholders})`);
        values.push(...value);
      } else if (typeof value === 'object' && value.operator) {
        // 支持操作符，如 { operator: '>', value: 10 }
        clauses.push(`${key} ${value.operator} ?`);
        values.push(value.value);
      } else {
        clauses.push(`${key} = ?`);
        values.push(value);
      }
    });

    const whereClause = `WHERE ${clauses.join(' AND ')}`;
    return { whereClause, values };
  }

  /**
   * 执行原始SQL查询
   */
  async raw(sql, params = []) {
    try {
      return await query(sql, params);
    } catch (error) {
      logger.error('执行原始SQL失败 / Failed to execute raw SQL', {
        sql: sql.substring(0, 100) + '...',
        error: error.message
      });
      throw error;
    }
  }
}

module.exports = BaseModel;